﻿using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OfficeOpenXml;

namespace Core
{
    public class ExportUtil
    {
        /// <summary>
        /// Exports the data table to excel.
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="downloadFileName">Name of the download file.</param>
        public static void ExportDataTableToExcel(DataTable source, string downloadFileName)
        {
            //Create workbook
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            Sheet worksheet = hssfworkbook.CreateSheet("Sheet1");

            //Write Column Row
            Row excelRow = worksheet.CreateRow(0);
            for (int colIndex = 0; colIndex < source.Columns.Count; colIndex++)
            {
                excelRow.CreateCell(colIndex).SetCellValue(source.Columns[colIndex].ColumnName);
            }

            //Wriete Data Rows
            for (int rowIndex = 0; rowIndex < source.Rows.Count; rowIndex++)
            {
                excelRow = worksheet.CreateRow(rowIndex + 1);
                DataRow row = source.Rows[rowIndex];
                for (int colIndex = 0; colIndex < source.Columns.Count; colIndex++)
                {
                    Cell cell = excelRow.CreateCell(colIndex);
                    CellStyle cellStyle = hssfworkbook.CreateCellStyle();
                    Type rowType = row[colIndex].GetType();

                    switch (rowType.ToString())
                    {
                        case "System.String":
                            cell.SetCellValue(row[colIndex].ToString());
                            break;

                        case "System.DateTime":
                            cell.SetCellValue(Convert.ToDateTime(row[colIndex]));
                            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                            cell.CellStyle = cellStyle;
                            break;

                        case "System.Boolean":
                            cell.SetCellValue(Convert.ToBoolean(row[colIndex]));
                            break;

                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            cell.SetCellValue(Convert.ToInt32(row[colIndex]));
                            break;

                        case "System.Decimal":
                        case "System.Double":
                            cell.SetCellValue(Convert.ToDouble(row[colIndex]));
                            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");
                            cell.CellStyle = cellStyle;
                            break;

                        case "System.DBNull":
                            break;
                    }
                }
            }

            //Write the stream data of workbook to the save file
            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);

            //Prevent problem with SSL protocol
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", HttpContext.Current.Server.UrlEncode(downloadFileName)));
            HttpContext.Current.Response.AddHeader("content-length", ms.Length.ToString());
            HttpContext.Current.Response.ContentType = "application/xls";

            //Handle File Encoding
            HttpContext.Current.Response.BufferOutput = true;
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "UTF-8";

            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.Flush();
            ms.Close();
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Exports the data table to excel2007.
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="downloadFileName">Name of the download file.</param>
        public static void ExportDataTableToExcel2007(DataTable source, string downloadFileName)
        {
            //Create workbook
            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

            //Write Column Row
            for (int colIndex = 0; colIndex < source.Columns.Count; colIndex++)
            {
                worksheet.Cells[1, colIndex + 1].Value = source.Columns[colIndex].ColumnName;
            }

            //Wriete Data Rows
            for (int rowIndex = 0; rowIndex < source.Rows.Count; rowIndex++)
            {
                DataRow row = source.Rows[rowIndex];
                for (int colIndex = 0; colIndex < source.Columns.Count; colIndex++)
                {
                    Type rowType = row[colIndex].GetType();

                    switch (rowType.ToString())
                    {
                        case "System.String":
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Value = row[colIndex].ToString();
                            break;

                        case "System.DateTime":
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Value = Convert.ToDateTime(row[colIndex]);
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Style.Numberformat.Format = "m/d/yy h:mm";
                            break;

                        case "System.Boolean":
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Value = Convert.ToBoolean(row[colIndex]);
                            break;

                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Value = Convert.ToInt32(row[colIndex]);
                            break;

                        case "System.Decimal":
                        case "System.Double":
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Value = Convert.ToDouble(row[colIndex]);
                            worksheet.Cells[rowIndex + 2, colIndex + 1].Style.Numberformat.Format = "#,##0.00";
                            break;

                        case "System.DBNull":
                            break;
                    }
                }
            }

            //Write the stream data of workbook to the save file
            MemoryStream ms = new MemoryStream();
            package.SaveAs(ms);

            //Prevent problem with SSL protocol
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", HttpContext.Current.Server.UrlEncode(downloadFileName)));
            HttpContext.Current.Response.AddHeader("content-length", ms.Length.ToString());
            HttpContext.Current.Response.ContentType = "application/xls";

            //Handle File Encoding
            HttpContext.Current.Response.BufferOutput = true;
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "UTF-8";

            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.Flush();
            ms.Close();
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Exports the data table to excel XML.
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        public static void ExportDataTableToExcelXML(DataTable source, string fileName)
        {
            //Prevent problem with SSL protocol
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", fileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.Write("<style>.text { mso-number-format:\\@; } </style>");

            //Handle File Encoding
            HttpContext.Current.Response.BufferOutput = true;
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "UTF-8";

            #region "Convert Engine"

            StringWriter excelDoc = new StringWriter();

            const string startExcelXML = "<xml version>\r\n<Workbook " +
                  "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                  "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                  "office:spreadsheet\">\r\n <Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                  "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                  "\r\n <Protection/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                  "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                  "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                  " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                  "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                  "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                  "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                  "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int x = 0; x < source.Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Columns[x].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");

            foreach (DataRow x in source.Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y < source.Columns.Count; y++)
                {
                    Type rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = XMLDate.Year.ToString() +
                                                     "-" +
                                                     (XMLDate.Month < 10 ? "0" +
                                                                           XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                                     "-" +
                                                     (XMLDate.Day < 10 ? "0" +
                                                                         XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                                     "T" +
                                                     (XMLDate.Hour < 10 ? "0" +
                                                                          XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                                     ":" +
                                                     (XMLDate.Minute < 10 ? "0" +
                                                                            XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                                     ":" +
                                                     (XMLDate.Second < 10 ? "0" +
                                                                            XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                                     ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();

            #endregion

            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">");
            HttpContext.Current.Response.Write(excelDoc.ToString());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Exports the data table to CSV.
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="downloadFileName">Name of the download file.</param>
        public static void ExportDataTableToCSV(DataTable source, string downloadFileName)
        {
            //Prevent problem with SSL protocol
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", downloadFileName));
            HttpContext.Current.Response.ContentType = "text/csv";

            //Handle File Encoding
            HttpContext.Current.Response.BufferOutput = true;
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "UTF-8";

            #region "Convert Engine"

            StringWriter csvDoc = new StringWriter();

            int iColCount = source.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                csvDoc.Write(source.Columns[i]);
                if (i < iColCount - 1)
                {
                    csvDoc.Write(",");
                }
            }

            csvDoc.Write(csvDoc.NewLine);
            // Now write all the rows.
            foreach (DataRow dr in source.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        csvDoc.Write(dr[i].ToString());
                    }
                    if (i < iColCount - 1)
                    {
                        csvDoc.Write(",");
                    }
                }
                csvDoc.Write(csvDoc.NewLine);
            }

            csvDoc.Close();

            #endregion

            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write(csvDoc.ToString());
            HttpContext.Current.Response.End();
        }

        public static StringWriter WebExportExcel(DataTable data, string filename)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.Charset = "";
            response.ContentEncoding = Encoding.Unicode;
            response.BinaryWrite(Encoding.Unicode.GetPreamble());
            response.ContentType = "application/vnd.ms-excel.xls";
            response.AddHeader("content-disposition", "attachment; filename=" + filename + ".xls");
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            GridView dg = new GridView { DataSource = data };
            dg.DataBind();
            dg.RenderControl(htw);

            return sw;
        }
    }
}
